# 定义数据库 工具类
import pymysql


class DBTools:
    # 建立连接
    @staticmethod
    def create_conn():
        # 指定 ip、port、用户名、密码、数据库，返回连接
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student", password="iHRM_student_2022",
                               database="ihrm",charset="utf8")
        return conn

    # 查询 方法
    @staticmethod
    def query_one(sql):
        cursor = None
        conn = None
        res = None
        try:
            # 获取连接，创建游标，执行SQL，提结果集，返回一条结果
            conn = DBTools.create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            print("查询sql失败",err)
        finally:
            cursor.close()
            conn.close()
            return res



    @staticmethod
    def query_many(sql,n):
        cursor = None
        conn = None
        res = None
        try:
            # 获取连接，创建游标，执行SQL，提结果集，返回多条结果
            conn = DBTools.create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(n)  # 借助形参，让用户指定查询的条数
        except Exception as err:
            print("查询多条sql失败",err)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def query_all(sql):
        cursor = None
        conn = None
        res = None
        try:
            # 获取连接，创建游标，执行SQL，提结果集，返回全部结果
            conn = DBTools.create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            cursor.rownumber = 0
            res = cursor.fetchall()
        except Exception as err:
            print("查询全部数据失败",err)
        finally:
            cursor.close()
            conn.close()
            return res



    @staticmethod
    # 增删改 方法
    def db_idu(sql):
        cursor = None
        conn = None
        try:
            # 获取连接，创建游标，执行SQL，提交/回滚 事务。
            conn = DBTools.create_conn()
            cursor = conn.cursor()
            ret = cursor.execute(sql)
            print("Affected rows", ret)
            conn.commit()
        except Exception as err:
            conn.rollback()
            print("增删改sql失败",err)
        finally:
            cursor.close()
            conn.close()





if __name__ == '__main__':
    res = DBTools.query_one("Select * from t_book where id = 500;")
    print("查一条",res)

    res1 = DBTools.query_many("Select * from t_book;", 3)
    print("查3条",res1)

    res2 = DBTools.query_all("Select * from t_book;")
    print("查询全部数据", res2)

    # 测试插入
    DBTools.db_idu("insert into t_book(id, title, pub_date) VALUES(509, '巴拉拉小魔仙', '1986-01-01');")

    # 测试update
    DBTools.db_idu("UPDATE t_book set `read` = `read` + 1 where title = '巴拉拉小魔仙';")
